﻿using DbJobsApi.Dto;
using DbJobsApi.Dto.SqlServer;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using System.Data.SqlClient;
using DbJobsApi.Extensions;
using System.Data;

namespace DbJobsApi.Controllers
{
    [Route("/api/[controller]/[action]")]
    [ApiController]
    public class SqlServerController : ControllerBase
    {
        private readonly ILogger<SqlServerController> logger;
        public SqlServerController(ILogger<SqlServerController> logger)
        {
            this.logger = logger;
        }

        /// <summary>
        /// 获取指定库的所有表
        /// </summary>
        /// <returns></returns>
        [HttpPost]
        public PageResult<CustomTableEntity> GetTables(SearchTableEntity entity)
        {
            using var conn = new SqlConnection(entity.DbConnStr);

            var list = conn.SqlServerQuery<CustomTableEntity>(
                @"select
a.name 'TABLE_NAME', 
case a.xtype when 'U' then 'TABLE' when 'V' then 'VIEW' when 'FN' then 'FUNCTION' when 'P' then 'PROCEDURE' else '?' end 'TABLE_TYPE',
a.crdate 'CREATED',
b.value 'COMMENTS'
from sysobjects a 
left join sys.extended_properties b on a.id = b.major_id and b.minor_id=0
where a.xtype in ('U', 'V', 'FN', 'P')");

            if (list == null)
                list = new();

            var whereList = list.AsQueryable();
            if (!string.IsNullOrEmpty(entity.TABLE_NAME))
            {
                whereList = whereList.Where(m => m.TABLE_NAME.Contains(entity.TABLE_NAME));
            }

            var records = list.Count;
            var result = new PageResult<CustomTableEntity>
            {
                rows = whereList
                        .OrderBy(entity.sidx, entity.sord)
                        .Skip((entity.page - 1) * entity.rows)
                        .Take(entity.rows).ToList(),
                records = records,
                page = entity.page,
                total = records / entity.rows + (records % entity.rows != 0 ? 1 : 0)
            };
            return result;
        }


        public class _TEMP
        {
            public string name { get; set; }
            public string type { get; set; }
            public int notnull { get; set; }
            public object dflt_value { get; set; }
            public int pk { get; set; }
        }
        /// <summary>
        /// 获取指定表的结构信息
        /// </summary>
        /// <param name="entity"></param>
        /// <returns></returns>
        [HttpPost]
        public List<TableColumnEntity> GetTable(GetTableEntity entity)
        {
            using var conn = new SqlConnection(entity.DbConnStr);
            List<TableColumnEntity> result = new ();
            try
            {
                var param = new Dictionary<string, object>
                {
                    { "@tableName", entity.TABLE_NAME }
                };
                result = conn.SqlServerQuery<TableColumnEntity>($@"SELECT 
NO = A.COLORDER, --字段序号
COLUMN_NAME = A.NAME, --字段名
AUTOIN = CASE WHEN COLUMNPROPERTY(A.ID, A.NAME, 'ISIDENTITY ') = 1 THEN 1 ELSE 0 END, --标识
ISKEY = CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE = 'PK ' AND PARENT_OBJ = A.ID AND NAME IN(
SELECT NAME FROM SYSINDEXES WHERE INDID IN(
SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID = A.COLID))) THEN 1 ELSE 0 END, --主键
DATA_TYPE = B.NAME, --数据类型
DATA_LENGTH = COLUMNPROPERTY(A.ID, A.NAME, 'PRECISION '), --长度
DATA_SCALE = COLUMNPROPERTY(A.ID, A.NAME, 'SCALE '), --小数位数
NULLABLE = CASE WHEN A.ISNULLABLE=1 THEN 'Y' ELSE 'N' END, --允许为空
DATA_DEFAULT = E.TEXT, null, --默认值
COMMENTS = ISNULL(G.[VALUE], ' ')--字段说明
FROM
SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE
INNER JOIN SYSOBJECTS D ON A.ID = D.ID   AND D.XTYPE = 'U ' AND   D.NAME <> 'DTPROPERTIES '
LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT = E.ID
LEFT JOIN sys.extended_properties G ON A.ID = G.major_id AND A.COLID = G.minor_id
LEFT JOIN sys.extended_properties F ON D.ID = F.major_id AND F.minor_id = 0
where d.NAME = @tableName", param);

                result.ForEach(m =>
                {
                    if(m.DATA_LENGTH == -1 && (m.DATA_TYPE == "varchar" || m.DATA_TYPE == "nvarchar" || m.DATA_TYPE == "varbinary"))
                    {
                        m.DATA_LENGTH =null;
                        m.DATA_TYPE = $"{m.DATA_TYPE}(max)";
                    }
                    if(m.DATA_TYPE != "numeric" && m.DATA_TYPE != "decimal")
                    {
                        m.DATA_SCALE = null;
                    }
                });

                if (result == null || result.Count == 0)
                {
                    return result;
                }
            }
            catch (Exception ex)
            {
                logger.LogInformation($"{ex.Message}");
                result = new List<TableColumnEntity>();
            }
            return result;
        }


        /// <summary>
        /// 编辑表结构定义 //此方法存在注入风险
        /// </summary>
        /// <param name="entity"></param>
        [HttpPost]
        public FlagEntity Edit(CustomTableEntity entity)
        {
            using var conn = new SqlConnection(entity.DbConnStr);
            try
            {
                var tableInfo = GetTables(new()
                {
                    page = 1,
                    sidx = "TABLE_NAME",
                    rows = 1,
                    sord = "asc",
                    DbName = entity.DbName,
                    DbConnStr = entity.DbConnStr,
                    TABLE_NAME = string.IsNullOrEmpty(entity.EDIT_TABLE_NAME) ? entity.TABLE_NAME : entity.EDIT_TABLE_NAME
                }).rows;

                if (tableInfo == null || tableInfo.Count == 0)
                {
                    //建表
                    var colSql = entity.Columns.Select(m => m.COLUMN_NAME_SQL).ToList();
                    if (entity.Columns.Any(m => m.ISKEY == 1))
                    {
                        colSql.Add($"CONSTRAINT {entity.TABLE_NAME}_PK PRIMARY KEY ( {Environment.NewLine}" +
                            $"{string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => $"[{m.COLUMN_NAME}]"))} {Environment.NewLine}" +
                            $" ){Environment.NewLine}");
                    }
                    _ = conn.SqlServerExcute($"CREATE TABLE [{entity.TABLE_NAME}] ( {Environment.NewLine}{string.Join(",", colSql)} {Environment.NewLine})");

                    _ = conn.SqlServerExcute($@"
IF ((SELECT COUNT(*) FROM ::fn_listextendedproperty('MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'{entity.TABLE_NAME}', NULL, NULL)) > 0)
  EXEC sp_updateextendedproperty 'MS_Description', N'{entity.COMMENTS.Replace("'","''")}', 'SCHEMA', N'dbo', 'TABLE', N'{entity.TABLE_NAME}'
ELSE
  EXEC sp_addextendedproperty 'MS_Description', N'{entity.COMMENTS.Replace("'", "''")}', 'SCHEMA', N'dbo', 'TABLE', N'{entity.TABLE_NAME}'");

                    foreach (var m in entity.Columns)
                    {
                        _ = conn.SqlServerExcute($@"
IF ((SELECT COUNT(*) FROM ::fn_listextendedproperty('MS_Description','SCHEMA', N'dbo','TABLE', N'{entity.TABLE_NAME}','COLUMN', N'{m.COLUMN_NAME}')) > 0)
  EXEC sp_updateextendedproperty'MS_Description', N'{m.COMMENTS.Replace("'", "''")}','SCHEMA', N'dbo','TABLE', N'{entity.TABLE_NAME}','COLUMN', N'{m.COLUMN_NAME}'
ELSE
  EXEC sp_addextendedproperty'MS_Description', N'{m.COMMENTS.Replace("'", "''")}','SCHEMA', N'dbo','TABLE', N'{entity.TABLE_NAME}','COLUMN', N'{m.COLUMN_NAME}'");
                    }
                }
                else if (tableInfo.Count == 1)
                {
                    var columns = GetTable(new() { DbConnStr = entity.DbConnStr, DbName = entity.DbName, TABLE_NAME = entity.EDIT_TABLE_NAME });

                    //修改表名
                    if (!entity.EDIT_TABLE_NAME.Equals(entity.TABLE_NAME))
                    {
                        logger.LogInformation($"表名发生变化");
                        _ = conn.SqlServerExcute($"ALTER TABLE \"{entity.EDIT_TABLE_NAME}\" RENAME TO \"{entity.TABLE_NAME}\"");
                    }
                    //重命名的字段
                    var renames = entity.Columns.Where(m => !string.IsNullOrEmpty(m.EDIT_COLUMN_NAME) && !m.EDIT_COLUMN_NAME.Equals(m.COLUMN_NAME))
                                    .ToList();
                    if (renames.Count > 0)
                    {
                        logger.LogInformation($"修改字段名:{renames.Count}");
                        foreach (var m in renames)
                        {
                            _ = conn.SqlServerExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" RENAME COLUMN \"{m.EDIT_COLUMN_NAME}\" TO \"{m.COLUMN_NAME}\"");
                        }
                    }

                    //任一属性被修改则要alter字段
                    var edits = (from a in columns
                                 join b in entity.Columns on a.COLUMN_NAME equals b.EDIT_COLUMN_NAME
                                 where a.DATA_TYPE != b.DATA_TYPE ||
                                 a.DATA_LENGTH != b.DATA_LENGTH ||
                                 a.DATA_SCALE != b.DATA_SCALE ||
                                 a.DATA_DEFAULT != b.DATA_DEFAULT ||
                                 a.NULLABLE != b.NULLABLE
                                 select $"ALTER TABLE [{entity.TABLE_NAME}] ALTER COLUMN {b.COLUMN_NAME_SQL}")
                                .ToList();
                    if (edits.Count > 0)
                    {
                        logger.LogInformation($"修改字段属性:{edits.Count}");
                        edits.ForEach(m => conn.SqlServerExcute(m));
                    }
                    //新结构不存在则删除字段
                    var dels = (from a in columns
                                join b in entity.Columns on a.COLUMN_NAME equals b.EDIT_COLUMN_NAME into bb
                                from bbb in bb.DefaultIfEmpty()
                                where bbb == null
                                select a.COLUMN_NAME)
                               .Select(m => $"ALTER TABLE \"{entity.TABLE_NAME}\" DROP COLUMN \"{m}\"")
                               .ToList();
                    if (dels.Count > 0)
                    {
                        logger.LogInformation($"删除字段:{dels.Count}");
                        foreach (var del in dels)
                        {
                            _ = conn.SqlServerExcute(del, null);
                        }
                    }

                    //结构没有原始列名则是新增
                    var adds = entity.Columns.Where(m => string.IsNullOrEmpty(m.EDIT_COLUMN_NAME))
                                    .Select(m => $"ALTER TABLE \"{entity.TABLE_NAME}\" ADD {m.COLUMN_NAME_SQL}")
                                    .ToList();
                    if (adds.Count > 0)
                    {
                        logger.LogInformation($"新增字段:{adds.Count}");
                        foreach (var add in adds)
                        {
                            _ = conn.SqlServerExcute(add);
                        }
                    }


                    //主键变化
                    var keyChange = !string.Join(",", columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME)).Equals(
                                string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME))
                                );
                    if (keyChange)
                    {
                        logger.LogInformation($"主键发生变化");
                        var oldKeyName = columns.FirstOrDefault(m => m.ISKEY == 1)?.CONSTRAINT_NAME;
                        if (!string.IsNullOrEmpty(oldKeyName))
                        {
                            _ = conn.SqlServerExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" DROP CONSTRAINT \"{oldKeyName}\"");
                        }
                        var newKeys = string.Join(",", entity.Columns.Where(m => m.ISKEY == 1).Select(m => m.COLUMN_NAME));
                        if (!string.IsNullOrEmpty(newKeys))
                        {
                            _ = conn.SqlServerExcute($"ALTER TABLE \"{entity.TABLE_NAME}\" {Environment.NewLine} " +
                                $"ADD CONSTRAINT \"{entity.TABLE_NAME}_PK PRIMARY\" KEY ({newKeys})");
                        }
                    }
                    //备注发生变化
                    if (tableInfo.First().COMMENTS != entity.COMMENTS)
                    {
                        logger.LogInformation($"表备注发生变化");
                        _ = conn.SqlServerExcute($@"
IF ((SELECT COUNT(*) FROM ::fn_listextendedproperty('MS_Description', 'SCHEMA', N'dbo', 'TABLE', N'{entity.TABLE_NAME}', NULL, NULL)) > 0)
  EXEC sp_updateextendedproperty 'MS_Description', N'{entity.COMMENTS.Replace("'", "''")}', 'SCHEMA', N'dbo', 'TABLE', N'{entity.TABLE_NAME}'
ELSE
  EXEC sp_addextendedproperty 'MS_Description', N'{entity.COMMENTS.Replace("'", "''")}', 'SCHEMA', N'dbo', 'TABLE', N'{entity.TABLE_NAME}'");
                    }
                    var comments = (from b in entity.Columns
                                    join a in columns on b.EDIT_COLUMN_NAME equals a.COLUMN_NAME into aa
                                    from aaa in aa.DefaultIfEmpty()
                                    where aaa == null || aaa.COMMENTS != b.COMMENTS
                                    select b)
                                   .ToList();
                    if (comments.Count > 0)
                    {
                        logger.LogInformation($"字段备注发生变化:{comments.Count}");
                        foreach (var m in comments)
                        {
                            _ = conn.SqlServerExcute($@"
IF ((SELECT COUNT(*) FROM ::fn_listextendedproperty('MS_Description','SCHEMA', N'dbo','TABLE', N'{entity.TABLE_NAME}','COLUMN', N'{m.COLUMN_NAME}')) > 0)
  EXEC sp_updateextendedproperty'MS_Description', N'{m.COMMENTS.Replace("'", "''")}','SCHEMA', N'dbo','TABLE', N'{entity.TABLE_NAME}','COLUMN', N'{m.COLUMN_NAME}'
ELSE
  EXEC sp_addextendedproperty'MS_Description', N'{m.COMMENTS.Replace("'", "''")}','SCHEMA', N'dbo','TABLE', N'{entity.TABLE_NAME}','COLUMN', N'{m.COLUMN_NAME}'");
                        }
                    }

                }
                else
                {
                    return new() { flag = false, msg = "你看到的表不是新的，请刷新列表重新编辑" };
                }
            }
            catch (Exception ex)
            {
                return new() { flag = false, msg = ex.Message };
            }
            return new() { flag = true };
        }

        [HttpPost]
        public FlagEntity GetMetaData(GetMetaEntity entity)
        {
            using var conn = new SqlConnection(entity.DbConnStr);

            var param = new Dictionary<string, object>
                {
                    { "@name", entity.Name }
                };
            var sql = conn.SqlServerExcuteField(
                @$"select b.definition from
sysobjects a left join sys.sql_modules b on a.id=b.object_id
where a.Name=@name", param);

            var flag = !string.IsNullOrEmpty(sql);
            if (flag)
            {
                sql = @$"IF EXISTS(SELECT 1 FROM sys.views WHERE name = '{entity.Name}')
DROP VIEW {entity.Name}
GO

{sql}";
            }

            return new FlagEntity
            {
                flag = !string.IsNullOrEmpty(sql),
                data = sql,
                msg = !string.IsNullOrEmpty(sql) ? $"{entity.Name}加载成功" : "查询不到信息"
            };
        }

        [HttpPost]
        public object RunSql(RunSqlEntity entity)
        {
            using var conn = new SqlConnection(entity.DbConnStr);

            var dts = conn.SqlServerQueryDataTable(entity.Sqls, out string[] logs);

            var tables = new List<object>();
            if (dts?.Length > 0)
            {
                foreach (var dt in dts)
                {
                    var columns = new List<object>();
                    for (int i = 0; i < dt?.Columns.Count; i++)
                    {
                        var col = dt.Columns[i];
                        columns.Add(new { name = col.ColumnName, type = col.DataType.Name });
                    }
                    tables.Add(new { columns, data = dt });
                }
            }

            return new
            {
                flag = true,
                tables,
                logs
            };
        }
    }
}
